#!/usr/bin/env python
# -*- coding: utf-8 -*-
# File  :new_attendance.py
# Time  :2025/5/27 15:39
# Email :fortunatedong@163.com
# Author:by Adonli
import calendar
import os
import re
from datetime import date,datetime,timedelta

import toml
from openpyxl.workbook import Workbook

from settings.attendance import load_current_staff_sort
import pandas as pd
from config import ROOT_DIR
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
configs_toml = os.path.join(ROOT_DIR, 'settings','configs.toml')
today = datetime.today()
current_year = today.year
current_month = today.month
last_month = today.month - 1
current_attendance_dir = os.path.join(ROOT_DIR, 'Datas/hr_attendance')
with open(configs_toml, "r", encoding="utf-8") as f:
    cfg = toml.load(f)
attendance_file_name = cfg['HRTASK']['HRAttendanceName']
current_attendance_file_path = f"{current_attendance_dir}/{attendance_file_name}"
staff_sort_xlsx_path = f"{current_attendance_dir}/{current_year}考勤表.xlsx"
pd.set_option('display.max_rows', 1000)
pd.set_option('display.max_columns', 1000)
pd.set_option('display.width', 1000)

current_staff_sort = load_current_staff_sort()
# print(current_staff_sort)

def load_staff_sort_xlsx(staff_sort_xlsx_path):
    staff_sort_table = [{"department":i["department"],"staff":j["name"]} for i in current_staff_sort for j in i["staff"]]
    staff_sort_df = pd.DataFrame(staff_sort_table)

    # 如果文件不存在，创建新文件并写入数据
    if not os.path.exists(staff_sort_xlsx_path):
        sheetnames = [f"{current_year}年{i}月" for i in range(1,13)]
        with pd.ExcelWriter(staff_sort_xlsx_path, engine='openpyxl') as writer:
            staff_sort_df.to_excel(writer, sheet_name='部门员工顺序原表', index=False)
            # 获取工作簿对象
            workbook = writer.book
            # 删除 openpyxl 自动创建的默认空 Sheet
            if 'Sheet' in workbook.sheetnames:
                del workbook['Sheet']
            workbook = writer.book
            for sheet_name in sheetnames:
                if sheet_name not in workbook.sheetnames:
                    workbook.create_sheet(sheet_name)
        return staff_sort_df
    # 如果文件存在，读取`table`页的内容
    else:
        try:
            # 读取Excel文件
            df = pd.read_excel(staff_sort_xlsx_path, sheet_name="部门员工顺序原表")
            # print(f"文件 {staff_sort_xlsx_path} 存在，已读取 【部门员工顺序原表】 工作表数据")
            return df
        except Exception as e:
            raise ValueError(f"读取 {staff_sort_xlsx_path} 的【部门员工顺序原表】工作表失败: {e}")



def load_attendance_file(attendance_file_path):
    df = pd.read_excel(attendance_file_path)
    copy_df = df.copy()
    # st.write(df)
    return copy_df
current_df = load_attendance_file(current_attendance_file_path)

"""
获取标题 广州-月度汇总 统计日期：2025-03-01 至 2025-03-31
"""
def get_attendance_title(df):
    return df.columns[0]
"""
获取详细的考勤信息表头
"""
def get_attendance_date(month):
    attendance_title = get_attendance_title(current_df)
    # print(attendance_title)
    # 提取年份和月份
    match = re.search(r'(\d{4})-(\d{2})-\d{2}', attendance_title)
    year = int(match.group(1))
    month = month

    # 生成该月的所有天数
    month_days = calendar.monthrange(year, month)[1]

    # 星期中文名称列表，按isoweekday顺序周一到周日对应索引0到6
    weekdays_chinese = ['星期一', '星期二', '星期三', '星期四', '星期五', '星期六', '星期日']

    date_weekdays = []
    for day in range(1, month_days + 1):
        d = date(year, month, day)
        weekday_num = d.isoweekday()  # 返回1（周一）到7（周日）
        weekday = weekdays_chinese[weekday_num - 1]
        date_weekdays.append(f"{month}/{day}-{weekday}")
    return date_weekdays

"""
获取表格详细内容
"""


def group_consecutive_dates(date_list):
    """将连续日期分组，但保留带特殊标记的日期（如'3/4半'）"""
    if not date_list:
        return []

    # 步骤1：去重并分类（普通日期 / 特殊标记日期）
    normal_dates = []  # 存储普通日期（可合并的）
    special_dates = []  # 存储带特殊标记的日期（不合并）
    seen = set()

    for date_str in date_list:
        if date_str in seen:
            continue
        seen.add(date_str)

        # 检查是否是特殊标记日期（如'3/4半'）
        if any(c.isalpha() or c in {'半', '早', '晚'} for c in date_str):
            special_dates.append(date_str)
        else:
            try:
                normal_dates.append(datetime.strptime(date_str, "%m/%d"))
            except ValueError:
                continue  # 忽略无效日期

    # 步骤2：普通日期按时间排序
    normal_dates.sort()

    grouped = []

    # 步骤3：合并连续普通日期
    if normal_dates:
        start_date = end_date = normal_dates[0]

        for current_date in normal_dates[1:]:
            if current_date == end_date + timedelta(days=1):
                end_date = current_date
            else:
                # 保存当前组
                if start_date == end_date:
                    grouped.append(start_date.strftime("%m/%d"))
                else:
                    grouped.append(f"{start_date.strftime('%m/%d')}-{end_date.strftime('%m/%d')}")
                start_date = end_date = current_date

        # 处理最后一组普通日期
        if start_date == end_date:
            grouped.append(start_date.strftime("%m/%d"))
        else:
            grouped.append(f"{start_date.strftime('%m/%d')}-{end_date.strftime('%m/%d')}")

    # 步骤4：合并特殊标记日期（保持原样）
    grouped.extend(special_dates)

    # 步骤5：按原始顺序排序（可选，如果需要保持输入顺序）
    # 这里假设输入顺序不重要，直接返回合并后的结果
    return grouped
def get_attendance_detail(df,month):
    attendance_detail = df.iloc[3:].copy()
    # print(attendance_detail)
    attendance_detail_columns = ["姓名","考勤组","部门","工号","职位","UserId","出勤天数","休息天数","工作时长","迟到","迟到时长","严重迟到次数","严重迟到时长","早退","早退时长","上班缺卡次数","下班缺卡次数","旷工","出差时长","外出时长",
                                 "事假","婚假","年假","产假","陪产假","丧假","苏州事假(天)","苏州病假(天)","苏州年假(天)","苏州调休(小时)","苏州婚假(天)","苏州产假(天)","苏州陪产假(天)","苏州丧假(天)","苏州育儿假(天)","病假(天)","调休(天)",
                                 "加班总时长","工作日（转加班费）","休息日（转加班费）","节假日（转加班费）","工作日（转调休）","休息日（转调休）","节假日（转调休）",
                                 ]
    date_weekdays = get_attendance_date(month)
    # print(date_weekdays)
    attendance_detail_columns += date_weekdays
    print(attendance_detail_columns)
    # print(attendance_detail.columns)
    attendance_detail.columns = attendance_detail_columns
    attendance_detail.reset_index(drop=True, inplace=True)
    # print(attendance_detail.columns)
    attendance_detail['备注'] = ''
    for index,row in attendance_detail.iterrows():
        note_parts = []

        # 1. 处理迟到
        late_dates = []
        for column in date_weekdays:
            record = str(row[column])
            if '迟到' in record:
                late_dates.append(column.split('-')[0])

        if late_dates:
            grouped = group_consecutive_dates(late_dates)
            if grouped:
                note_parts.append(f"{'，'.join(grouped)}迟到")

        # 2. 处理婚假
        marriage_dates = []
        for column in date_weekdays:
            record = str(row[column])
            if '婚假' in record:
                marriage_dates.append(column.split('-')[0])

        if marriage_dates:
            grouped = group_consecutive_dates(marriage_dates)
            if grouped:
                note_parts.append(f"{'，'.join(grouped)}婚假")

        # 3. 处理产假（包含产假和陪产假）
        maternity_dates = []
        for column in date_weekdays:
            record = str(row[column])
            if '产假' in record or '陪产假' in record:
                maternity_dates.append(column.split('-')[0])

        if maternity_dates:
            grouped = group_consecutive_dates(maternity_dates)
            if grouped:
                note_parts.append(f"{'，'.join(grouped)}产假")

        # 4. 处理请假（包含事假和年假）
        leave_dates = []
        for column in date_weekdays:
            record = str(row[column])
            if '事假' in record or '年假' in record:
                matches = re.findall(r'\d+\.?\d*天', record)
                print(matches)
                if len(matches) == 1 and matches[0] == "0.5天":
                    leave_dates.append(column.split('-')[0]+'半')
                else:
                    leave_dates.append(column.split('-')[0])
        print(leave_dates)

        if leave_dates:
            grouped = group_consecutive_dates(leave_dates)
            if grouped:
                note_parts.append(f"{'，'.join(grouped)}请假")

        # 更新备注列
        attendance_detail.at[index, '备注'] = '；'.join(note_parts) if note_parts else ''
    # 重新排列列顺序，确保备注在最后
    cols_order = ['姓名',  '备注']
    copy_current_staff_sort_xlsx = attendance_detail[cols_order]
    # 保存到Excel
    wb = load_workbook(staff_sort_xlsx_path)
    ws = wb[f"{current_year}年{month}月"]

    # 清空原有数据（保留表头）
    for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
        for cell in row:
            cell.value = None

    # 写入新数据（包括表头）
    for r_idx, row in enumerate(dataframe_to_rows(copy_current_staff_sort_xlsx, index=False, header=True), 1):
        for c_idx, value in enumerate(row, 1):
            ws.cell(row=r_idx, column=c_idx, value=value)

    # 保存工作簿
    wb.save(staff_sort_xlsx_path)




    # # print(attendance_detail)
    # def merge_current_df_attendance_detail():
    #     # ===========================================================================================
    #     current_staff_sort_xlsx = load_staff_sort_xlsx(staff_sort_xlsx_path)
    #     copy_current_staff_sort_xlsx = current_staff_sort_xlsx.copy()
    #     copy_current_staff_sort_xlsx = pd.merge(
    #                                         copy_current_staff_sort_xlsx[['department', 'staff']],
    #                                         attendance_detail[['迟到', '早退', '旷工', '婚假', '产假', '陪产假','丧假', '年假', '事假','备注']],
    #                                         left_on="staff",
    #                                         right_on=attendance_detail['姓名'],
    #                                         how='left'
    #                                     )
    #     # ======================================================================================
    #     # copy_current_staff_sort_xlsx = attendance_detail[['部门','姓名','迟到', '早退', '旷工', '婚假', '产假', '陪产假','丧假', '年假', '事假','备注']]
    #     # copy_current_staff_sort_xlsx["部门"] = copy_current_staff_sort_xlsx["部门"].apply(lambda x: x.replace("广州金穗隆信息科技股份有限公司-",""))
    #     # 指定需要强制转换的列
    #     numeric_cols = ['迟到', '早退', '旷工', '婚假', '产假', '陪产假','丧假', '年假', '事假']
    #     # 强制转换为 float，并填充 NaN 为 0.0
    #     copy_current_staff_sort_xlsx[numeric_cols] = copy_current_staff_sort_xlsx[numeric_cols].apply(
    #         lambda x: pd.to_numeric(x, errors='coerce').fillna(0.0)
    #     )
    #     return copy_current_staff_sort_xlsx
    #
    # copy_current_staff_sort_xlsx = merge_current_df_attendance_detail().copy()
    # def read_last_year(staff_sort_xlsx_path):
    #     df = pd.read_excel(staff_sort_xlsx_path, sheet_name=f"{current_year}年{last_month-1}月")
    #     # print(df)
    #     return df
    # # copy_current_staff_sort_xlsx["产假/陪产假"] = 22.0 - copy_current_staff_sort_xlsx["产假"] + copy_current_staff_sort_xlsx["陪产假"]
    # copy_current_staff_sort_xlsx["计薪天数"] = 22.0 - copy_current_staff_sort_xlsx["旷工"]-copy_current_staff_sort_xlsx["事假"]
    # copy_current_staff_sort_xlsx["前年假"] = read_last_year(staff_sort_xlsx_path)["余年假"]
    # copy_current_staff_sort_xlsx["余年假"] = copy_current_staff_sort_xlsx["前年假"] - copy_current_staff_sort_xlsx["年假"]
    # copy_current_staff_sort_xlsx["出勤天数"] = 22.0 - copy_current_staff_sort_xlsx["旷工"] - copy_current_staff_sort_xlsx["婚假"] - copy_current_staff_sort_xlsx["产假"] - copy_current_staff_sort_xlsx["丧假"] - copy_current_staff_sort_xlsx["年假"] - copy_current_staff_sort_xlsx["事假"]
    # # print(copy_current_staff_sort_xlsx)
    #
    # # 重新排列列顺序，确保备注在最后
    # cols_order = ['department', 'staff', '迟到', '早退', '旷工', '婚假', '产假', '陪产假', '丧假',
    #               '年假', '事假', '计薪天数', '前年假', '余年假', '出勤天数', '备注']
    # copy_current_staff_sort_xlsx = copy_current_staff_sort_xlsx[cols_order]
    #
    # # 保存到Excel
    # wb = load_workbook(staff_sort_xlsx_path)
    # ws = wb[f"{current_year}年{last_month}月"]
    #
    # # 清空原有数据（保留表头）
    # for row in ws.iter_rows(min_row=1, max_row=ws.max_row):
    #     for cell in row:
    #         cell.value = None
    #
    # # 写入新数据（包括表头）
    # for r_idx, row in enumerate(dataframe_to_rows(copy_current_staff_sort_xlsx, index=False, header=True), 1):
    #     for c_idx, value in enumerate(row, 1):
    #         ws.cell(row=r_idx, column=c_idx, value=value)
    #
    # # 保存工作簿
    # wb.save(staff_sort_xlsx_path)
    # # return copy_current_staff_sort_xlsx





# load_staff_sort_xlsx(staff_sort_xlsx_path)
# get_attendance_detail(current_df)


